--------------------------------------------------------
--  File created - Monday-October-21-2013   
--------------------------------------------------------
DROP TABLE "VIKTOR"."USERS" cascade constraints;
DROP TABLE "VIKTOR"."USER_GROUPS" cascade constraints;
--------------------------------------------------------
--  DDL for Sequence USERS_SEQ
--------------------------------------------------------

   CREATE SEQUENCE  "VIKTOR"."USERS_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 41 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Table USERS
--------------------------------------------------------

  CREATE TABLE "VIKTOR"."USERS" 
   (	"ID" NUMBER(9,0), 
	"LOGIN" VARCHAR2(50 BYTE), 
	"NAME" VARCHAR2(70 BYTE), 
	"SURNAME" VARCHAR2(70 BYTE), 
	"BIRTHDATE" DATE, 
	"PHONE" VARCHAR2(20 BYTE), 
	"REGISTRATION_DATE" DATE, 
	"GROUP_ID" NUMBER(9,1), 
	"BLOCKED" NUMBER(1,0) DEFAULT 0, 
	"REGISTRATION_TOKEN" VARCHAR2(256 CHAR), 
	"PASSWORD" VARCHAR2(64 CHAR), 
	"SALT" VARCHAR2(64 CHAR)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  DDL for Table USER_GROUPS
--------------------------------------------------------

  CREATE TABLE "VIKTOR"."USER_GROUPS" 
   (	"ID" NUMBER(9,0), 
	"NAME" VARCHAR2(30 BYTE), 
	"DISCOUNT" NUMBER(2,0), 
	"COMMISSION" NUMBER(2,0)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
REM INSERTING into VIKTOR.USERS
SET DEFINE OFF;
Insert into VIKTOR.USERS (ID,LOGIN,NAME,SURNAME,BIRTHDATE,PHONE,REGISTRATION_DATE,GROUP_ID,BLOCKED,REGISTRATION_TOKEN,PASSWORD,SALT) values (33,'nahtlol@gmail.com','qqq',null,null,'222',to_date('21-OCT-13','DD-MON-RR'),4,0,'cfb968c4-7561-4dab-a0cc-ffc90f5fab20','57CD12BA1A79AB2203E407116D152B4A0AF14D61796688FF3BD85415CA058FBF','F21415FEA49C126D7DB9F0D4FB738F8F260CCAFC5D4355BE5A7552636322A3B5');
Insert into VIKTOR.USERS (ID,LOGIN,NAME,SURNAME,BIRTHDATE,PHONE,REGISTRATION_DATE,GROUP_ID,BLOCKED,REGISTRATION_TOKEN,PASSWORD,SALT) values (35,'viktor.melnic@gmail.com','www',null,null,'222',to_date('21-OCT-13','DD-MON-RR'),4,0,'f3d5df64-8d0d-476f-8ea2-7d072db30888','9A1151333E2E0E203BE48A699AD37A38CB2C3738B8779FDD34C951DCBA99FBA7','81FCCFC6EE94708BE10277B6ABAE2381C72804D8FBB602A0C6A881F6CFFF1FBB');
--------------------------------------------------------
--  DDL for Index USERS_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "VIKTOR"."USERS_PK" ON "VIKTOR"."USERS" ("ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  Constraints for Table USERS
--------------------------------------------------------

  ALTER TABLE "VIKTOR"."USERS" MODIFY ("SALT" NOT NULL ENABLE);
  ALTER TABLE "VIKTOR"."USERS" MODIFY ("REGISTRATION_TOKEN" NOT NULL ENABLE);
  ALTER TABLE "VIKTOR"."USERS" MODIFY ("PASSWORD" NOT NULL ENABLE);
  ALTER TABLE "VIKTOR"."USERS" ADD CONSTRAINT "USERS_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE;
  ALTER TABLE "VIKTOR"."USERS" MODIFY ("BLOCKED" NOT NULL ENABLE);
  ALTER TABLE "VIKTOR"."USERS" MODIFY ("GROUP_ID" NOT NULL ENABLE);
  ALTER TABLE "VIKTOR"."USERS" MODIFY ("REGISTRATION_DATE" NOT NULL ENABLE);
  ALTER TABLE "VIKTOR"."USERS" MODIFY ("NAME" NOT NULL ENABLE);
  ALTER TABLE "VIKTOR"."USERS" MODIFY ("LOGIN" NOT NULL ENABLE);
  ALTER TABLE "VIKTOR"."USERS" MODIFY ("ID" NOT NULL ENABLE);
--------------------------------------------------------
--  Ref Constraints for Table USERS
--------------------------------------------------------

  ALTER TABLE "VIKTOR"."USERS" ADD CONSTRAINT "USERS_FK" FOREIGN KEY ("GROUP_ID")
	  REFERENCES "VIKTOR"."USER_GROUPS" ("ID") ON DELETE CASCADE ENABLE;
--------------------------------------------------------
--  DDL for Trigger TRIGGER1
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "VIKTOR"."TRIGGER1" 
   before insert on "VIKTOR"."USERS" 
   for each row 
begin  
   if inserting then 
      if :NEW."ID" is null then 
         select USERS_SEQ.nextval into :NEW."ID" from dual; 
      end if; 
   end if; 
end;

/
ALTER TRIGGER "VIKTOR"."TRIGGER1" ENABLE;
--------------------------------------------------------
--  DDL for Synonymn DUAL
--------------------------------------------------------

  CREATE OR REPLACE PUBLIC SYNONYM "DUAL" FOR "SYS"."DUAL";
